package love.yuanmengrobot.als.business.dao.impl;
/*
 *   @Studio:CHJY · 源梦科技
 *   @Author:CHJY创幻
 *   @Ide:IntelliJ IDEA
 *   @Creation time:2021/6/5 23:54
 */

//import love.yuanmengrobot.als.business.util.TrainPlan;

import love.yuanmengrobot.als.business.dao.ImportPlainDao;
import love.yuanmengrobot.als.business.javaBean.Alstrainplan;
import love.yuanmengrobot.als.business.javaBean.Alstrainplancourse;
import love.yuanmengrobot.als.business.javaBean.Major;
import love.yuanmengrobot.als.business.javaBean.StudentViewCredit;
import love.yuanmengrobot.als.business.util.TrainPlan;
import love.yuanmengrobot.als.utilPublic.JdbcUtil;
import love.yuanmengrobot.als.utilPublic.SqlString;
import love.yuanmengrobot.als.utilPublic.SqlStringImportPlain;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ImportPlainDaoImpl implements ImportPlainDao {
    @Override
    public ArrayList<Major> selectAllMajor() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Major major = null;
        ArrayList<Major> arrayList = new ArrayList<Major>();
        try {
            conn = JdbcUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(SqlStringImportPlain.selectMajor());
            while (rs.next()) {
                major = new Major();
                major.setMajorID(rs.getString("MAJOR_ID"));
                major.setMajorName(rs.getString("MAJOR_NAME"));
                arrayList.add(major);
            }

//            System.out.println("专业：" + arrayList);

            return arrayList;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {//回收资源
            JdbcUtil.close(rs, stmt, conn);
        }
        return null;
    }

    @Override
    public ArrayList<Alstrainplan> selectAllAlstrainplain(String majorId) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<Alstrainplan> arrayList = new ArrayList<Alstrainplan>();
        Alstrainplan alstrainplan = null;
        try {
            conn = JdbcUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(SqlStringImportPlain.selectTrainPlan(majorId));

            while (rs.next()) {
                alstrainplan = new Alstrainplan();
                alstrainplan.setTrainplanId(rs.getString("TRAINPLAN_ID"));
                alstrainplan.setMajorId(rs.getString("MAJOR_ID"));
                alstrainplan.setGrade(rs.getString("GRADE"));
                alstrainplan.setOptionalCredit(rs.getString("OPTIONAL_CREDIT"));
                alstrainplan.setLimiteCredit(rs.getString("LIMITE_CREDIT"));
                alstrainplan.setProfessionalRequired(rs.getString("PROFESSIONAL_REQUIRED"));
                alstrainplan.setGeneralRequired(rs.getString("GENERAL_REQUIRED"));
                alstrainplan.setGeneralElective(rs.getString("GENERAL_ELECTIVE"));
                alstrainplan.setDisciplineBasis(rs.getString("DISCIPLINE_BASIS"));
                alstrainplan.setPracticeLink(rs.getString("PRACTICE_LINK"));
                arrayList.add(alstrainplan);
            }
//            System.out.println("培养方案：" + arrayList);
            return arrayList;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {//回收资源
            JdbcUtil.close(rs, stmt, conn);
        }
        return null;
    }

    @Override
    public ArrayList<Alstrainplancourse> selectAllAlstrainplancourse(String trainplain) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<Alstrainplancourse> arrayList = new ArrayList<Alstrainplancourse>();
        Alstrainplancourse alstrainplancourse = null;
        try {
            conn = JdbcUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(SqlStringImportPlain.selectTrainPlancourse(trainplain));
            while (rs.next()) {

                alstrainplancourse = new Alstrainplancourse();
                alstrainplancourse.setTrainplancourseId(rs.getString("TRAINPLANCOURSE_ID"));
                alstrainplancourse.setTrainplanId(rs.getString("TRAINPLAN_ID"));
                alstrainplancourse.setCourseName(rs.getString("COURSE_NAME"));
                alstrainplancourse.setPlanCourseTerm(rs.getString("PLAN_COURSE_TERM"));
                alstrainplancourse.setCredit(rs.getString("CREDIT"));
                alstrainplancourse.setCourseType(rs.getString("COURSE_TYPE"));
                alstrainplancourse.setCourseHours(rs.getString("COURSE_HOURS"));
                alstrainplancourse.setCourseLevel(rs.getString("COURSE_LEVEL"));
                arrayList.add(alstrainplancourse);
            }

//            System.out.println("培养方案课程：" + arrayList);
            return arrayList;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {//回收资源
            JdbcUtil.close(rs, stmt, conn);
        }
        return null;
    }
    @Override
    public ArrayList<Alstrainplancourse> selectAllAlstrainplancourse(String majorID,String grade) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<Alstrainplancourse> arrayList = new ArrayList<Alstrainplancourse>();
        Alstrainplancourse alstrainplancourse = null;
        try {
            conn = JdbcUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(SqlStringImportPlain.selectePlainCourseByIdAndGrade(majorID,grade));
            while (rs.next()) {
                alstrainplancourse = new Alstrainplancourse();
                alstrainplancourse.setTrainplancourseId(rs.getString("TRAINPLANCOURSE_ID"));
                alstrainplancourse.setTrainplanId(rs.getString("TRAINPLAN_ID"));
                alstrainplancourse.setCourseName(rs.getString("COURSE_NAME"));
                alstrainplancourse.setPlanCourseTerm(rs.getString("PLAN_COURSE_TERM"));
                alstrainplancourse.setCredit(rs.getString("CREDIT"));
                alstrainplancourse.setCourseType(rs.getString("COURSE_TYPE"));
                alstrainplancourse.setCourseHours(rs.getString("COURSE_HOURS"));
                alstrainplancourse.setCourseLevel(rs.getString("COURSE_LEVEL"));
                arrayList.add(alstrainplancourse);
            }
            return arrayList;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {//回收资源
            JdbcUtil.close(rs, stmt, conn);
        }
        return null;
    }

    @Override
    public int insertMajor(String majorName) {

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConnection();
            stmt = conn.createStatement();
            int su = stmt.executeUpdate(SqlStringImportPlain.insertMajor(majorName));
            if (su == 1) {
                System.out.println("插入专业成功");
                return 1;
            } else {
                System.out.println("插入专业失败");
                return 0;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {//回收资源
            JdbcUtil.close(rs, stmt, conn);
        }


        return 0;
    }

    @Override
    public int insertAlstrainplain() {
        return 0;
    }

    @Override
    public int insertAlstrainplancourse() {
        return 0;
    }

    @Override
    public int deleteMajorById() {
        return 0;
    }

    @Override
    public int deleteAlstrainplainById() {
        return 0;
    }

    @Override
    public int deleteAlstrainplancourseById() {
        return 0;
    }

    @Override
    public int updateMajorById(String majorId, String majorName) {

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConnection();
            stmt = conn.createStatement();
            int su = stmt.executeUpdate(SqlStringImportPlain.updateMajor(majorId, majorName));
            if (su == 1) {
                System.out.println("修改专业成功");
                return 1;
            } else {
                System.out.println("修改专业失败");
                return 0;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {//回收资源
            JdbcUtil.close(rs, stmt, conn);
        }


        return 0;
    }

    @Override
    public int updateAlstrainplainById(Alstrainplan alstrainplan) {


        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConnection();
            stmt = conn.createStatement();
            int su = stmt.executeUpdate(SqlStringImportPlain.updatePlain(alstrainplan));
            if (su == 1) {
                System.out.println("修改方案成功");
                return 1;
            } else {
                System.out.println("修改方案失败");
                return 0;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {//回收资源
            JdbcUtil.close(rs, stmt, conn);
        }


        return 0;
    }

    @Override
    public int updateAlstrainplancourseByPlain(TrainPlan trainPlan, TrainPlan trainPlans[]) {
//        System.out.println("ssssssssssssss");
//        System.out.println(trainPlan);
//        for (TrainPlan TR : trainPlans) {
//
//            System.out.println(TR);
//        }
        String majorName = trainPlan.getMajorId();
        String grade=trainPlan.getGrade();

        //开始写入数据库,培养方案开始
        String sqlTrainPlan = "insert into ALSTRAINPLAN values(null,?,?,?,?,?,?,?,?,?)";//加入数据到
        String sqlIsMajorExist = "select MAJOR_ID " + "from MAJOR where MAJOR_NAME like '" + majorName + "'";//查询专业信息
        String sqlMajor = "insert into major values(null,?)";//插入数据到major专业表
        Connection conn = null;
//        String url = "jdbc:oracle:thin:@112.74.190.130:1521:orcl";
        Statement stmt = null;//SQL语句对象，拼SQL
        PreparedStatement pstmt = null;
        try {
//            Class.forName("oracle.jdbc.driver.OracleDriver");//
//            conn = DriverManager.getConnection(url, "scott", "scott1316");
            conn = JdbcUtil.getConnection();
            stmt = conn.createStatement();
            /*执行sql*/
            /*设置参数*/
            ResultSet rs = null;//验证数据结果集
            rs = stmt.executeQuery(sqlIsMajorExist);//判断专业是否存在
            String majorId = null;
            if (rs.next()) {//专业存在，开始插入培养方案
                majorId = rs.getString("MAJOR_ID");
//                trainPlan.setMajorId(r1);
                pstmt = conn.prepareStatement(sqlTrainPlan);
                pstmt.setString(1, majorId);
                pstmt.setString(2, grade);//如果该专业的该年级已经有了培养方案，就不应该插入成功！！
                pstmt.setDouble(3, Double.parseDouble(trainPlan.getOptionalCredit()));
                pstmt.setDouble(4, Double.parseDouble(trainPlan.getLimiteCredit()));
                pstmt.setDouble(5, Double.parseDouble(trainPlan.getProfessionalRequired()));
                pstmt.setDouble(6, Double.parseDouble(trainPlan.getGeneralRequired()));
                pstmt.setDouble(7, Double.parseDouble(trainPlan.getGeneralElective()));
                pstmt.setDouble(8, Double.parseDouble(trainPlan.getDisciplineBasis()));
                pstmt.setDouble(9, Double.parseDouble(trainPlan.getPracticeLink()));
                pstmt.executeUpdate();//
                System.out.println("添加培养方案成功");
            }
            String sqlGetId = "SELECT TRAINPLAN_ID FROM ALSTRAINPLAN WHERE MAJOR_ID=" + majorId + " AND GRADE=" + grade;
            String sqlInsertplanCourse = "insert into ALSTRAINPLANCOURSE values(null,?,?,?,?,?,?,?)";

            rs = stmt.executeQuery(sqlGetId);//获取培养方案的ID
            if (rs.next()) {//获取培养方案的ID存在，开始插入培养方案课程
                String planId = rs.getString("TRAINPLAN_ID");
                pstmt = conn.prepareStatement(sqlInsertplanCourse);
                for (TrainPlan TR : trainPlans) {
                    if (TR.getCourseType() == null) continue;
                    if (TR.getCourseType().equals("课程类别") && TR.getCourseLevel().equals("应修学分")) break;
                    pstmt.setString(1, planId);
                    pstmt.setString(2, TR.getCourseName());
                    pstmt.setString(3, TR.getPlanCourseTerm());
                    pstmt.setString(4, TR.getCredit());
                    pstmt.setString(5, TR.getCourseType());
                    pstmt.setString(6, TR.getCourseHours());
                    pstmt.setString(7, TR.getCourseLevel());
                    pstmt.addBatch();
                }
                pstmt.executeBatch(); //批量执行预定义SQL
                System.out.println("添加培养方案的课程成功");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }  finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


        return 0;
    }

    @Override
    public int updateAlstrainplancourseByAlstrainplancourse(Alstrainplancourse alstrainplancourse) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConnection();
            stmt = conn.createStatement();
            int su = stmt.executeUpdate(SqlStringImportPlain.updatePlainCourse(alstrainplancourse));
            if (su == 1) {
                System.out.println("修改课程成功");
                return 1;
            } else {
                System.out.println("修改课程失败");
                return 0;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {//回收资源
            JdbcUtil.close(rs, stmt, conn);
        }

        return 0;
    }


//    //开始写入数据库,培养方案开始
//    String sqlTrainPlan = "insert into ALSTRAINPLAN values(null,?,?,?,?,?,?,?,?,?)";//加入数据到score成绩表
//    String sqlIsMajorExist = "select MAJOR_ID "
//            + "from MAJOR where MAJOR_NAME like '" + majorName + "'";//查询专业信息
//    String sqlMajor = "insert into major values(null,?)";//插入数据到major专业表
//    Connection conn = null;
//    String url = "jdbc:oracle:thin:@112.74.190.130:1521:orcl";
//    Statement stmt = null;//SQL语句对象，拼SQL
//    PreparedStatement pstmt = null;
//        try {
//        Class.forName("oracle.jdbc.driver.OracleDriver");//
//        conn = DriverManager.getConnection(url, "scott", "scott1316");
//        stmt = conn.createStatement();
//        /*执行sql*/
//        /*设置参数*/
//        ResultSet rs = null;//验证数据结果集
//        rs = stmt.executeQuery(sqlIsMajorExist);//判断专业是否存在
//        String majorId = null;
//        if (rs.next()) {//专业存在，开始插入培养方案
//            majorId = rs.getString("MAJOR_ID");
////                trainPlan.setMajorId(r1);
//            pstmt = conn.prepareStatement(sqlTrainPlan);
//            pstmt.setString(1, majorId);
//            pstmt.setString(2, grade);
//            pstmt.setDouble(3, Double.parseDouble(trainPlan.getOptionalCredit()));
//            pstmt.setDouble(4, Double.parseDouble(trainPlan.getLimiteCredit()));
//            pstmt.setDouble(5, Double.parseDouble(trainPlan.getProfessionalRequired()));
//            pstmt.setDouble(6, Double.parseDouble(trainPlan.getGeneralRequired()));
//            pstmt.setDouble(7, Double.parseDouble(trainPlan.getGeneralElective()));
//            pstmt.setDouble(8, Double.parseDouble(trainPlan.getDisciplineBasis()));
//            pstmt.setDouble(9, Double.parseDouble(trainPlan.getPracticeLink()));
//            pstmt.executeUpdate();//
//            System.out.println("添加培养方案成功");
//        }
//        String sqlGetId = "SELECT TRAINPLAN_ID FROM ALSTRAINPLAN WHERE MAJOR_ID=" + majorId + " AND GRADE=" + grade;
//        String sqlInsertplanCourse = "insert into ALSTRAINPLANCOURSE values(null,?,?,?,?,?,?,?)";
//
//        rs = stmt.executeQuery(sqlGetId);//获取培养方案的ID
//        if (rs.next()) {//获取培养方案的ID存在，开始插入培养方案课程
//            String planId = rs.getString("TRAINPLAN_ID");
//            pstmt = conn.prepareStatement(sqlInsertplanCourse);
//            for (TrainPlan TR : trainPlans) {
//                if (TR.getCourseType() == null) continue;
//                if (TR.getCourseType().equals("课程类别") && TR.getCourseLevel().equals("应修学分")) break;
//                pstmt.setString(1, planId);
//                pstmt.setString(2, TR.getCourseName());
//                pstmt.setString(3, TR.getPlanCourseTerm());
//                pstmt.setString(4, TR.getCredit());
//                pstmt.setString(5, TR.getCourseType());
//                pstmt.setString(6, TR.getCourseHours());
//                pstmt.setString(7, TR.getCourseLevel());
//                pstmt.addBatch();
//            }
//            pstmt.executeBatch(); //批量执行预定义SQL
//            System.out.println("添加培养方案的课程成功");
//        }
//
////            String sql ="insert into "+table_name+" " +
////                    "(shop_name,shop_url,area,phone,address,ev_num,bad_num,score,pt_name,fp," +
////                    " open_date,city,sales,code,lon,lat,update_zt,img_zz_count,is_yyzz,is_xkz," +
////                    " is_lhfj,csdj,gldj,jcrq,dwmc,xkzh,fddbr,jydz,ztyt,jyfw," +
////                    " yxq,fl,img_url)" +
////                    " values" +
////                    "(?,?,?,?,?, ?,?,?,?,?," +
////                    " ?,?,?,?,?, ?,?,?,?,?," +
////                    " ?,?,?,?,?, ?,?,?,?,?," +
////                    " ?,?,?)";
////
////            pstmt = conn.prepareStatement(sql);
//
////                System.out.println("添加学生成功");
//
//
////            rs=stmt.executeQuery(sqlIsScoreExist);//判断学生成绩是否存在
////            if(!rs.next()) {
////                /*设置参数*/
////                pstmt=conn.prepareStatement(sqlScore);
////                pstmt.setString(1,getStu_no());
////                pstmt.setString(2,getCourse_id());
////                pstmt.setString(3,getScore());
////                pstmt.setString(4,getScore_type());
////                pstmt.setString(5,getCourse_code());
////                pstmt.setString(6,getCourse_term());
////                pstmt.setString(7,getCourse_name());
////                pstmt.setString(8,getTest_type());
////                pstmt.setDouble(9,getCredit());
////                pstmt.setString(10,getCourse_type());
////                pstmt.executeUpdate();//学生成绩不存在，插入数据
////                System.out.println("添加成绩成功");
////            }
//    } catch (SQLException e) {
//        e.printStackTrace();
//    } catch (ClassNotFoundException e) {
//        e.printStackTrace();
//    } finally {
//        try {
//            conn.close();
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//    }

}
